package cn.edu.sdjzu.xg.kcsj.security;


import cn.edu.sdjzu.xg.kcsj.util.CommonDao;
import cn.edu.sdjzu.xg.kcsj.util.Condition;
import cn.edu.sdjzu.xg.kcsj.util.JdbcHelper;
import cn.edu.sdjzu.xg.kcsj.util.Pagination;

import java.sql.*;
import java.util.Collection;
import java.util.List;
import java.util.TreeSet;

public class ResourceAssRoleDao {
    private static ResourceAssRoleDao resourceAssRoleDao = new ResourceAssRoleDao();
    private ResourceAssRoleDao() {}

    public static ResourceAssRoleDao getInstance() {
        return resourceAssRoleDao;
    }

    // 分页+条件
    public Collection<ResourceAssRole> findAll(List conditionList, Pagination pagination, Connection connection) throws SQLException {
        //创建集合类对象，用来保存所有记录代表的faculty对象
        Collection<ResourceAssRole> resourceAssRoles = new TreeSet<ResourceAssRole>();
        //准备预编译的语句
        int totalNum = CommonDao.getInstance().count(connection,"resource_ass_role");
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM resource_ass_role ");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        if(pagination!=null){
            String LimitClause = pagination.toLimitClause(totalNum);
            sql_select.append(LimitClause);
        }
        //执行预编译语句，结果集放在resultSet中
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = pstmt.executeQuery();
        //遍历resultSet
        while(resultSet.next()){
            resourceAssRoles.add(new ResourceAssRole(
                    resultSet.getInt("id"),
                    RoleService.getInstance().find(resultSet.getInt("role_id")),
                    ResourceService.getInstance().find(resultSet.getInt("resource_id"))
                    ));
        }
        JdbcHelper.close(resultSet,pstmt);
        return resourceAssRoles;
    }

    // 按id查一条
    public ResourceAssRole find(int id, Connection connection) throws SQLException {
        ResourceAssRole resourceAssRole = null;
        //创建sql语句
        String find_sql = "select * from resource_ass_role where id = ?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //根据id查找
        ResultSet resultSet = pstmt.executeQuery();
        while(resultSet.next()) {
            resourceAssRole = new ResourceAssRole(
                    id,
                    RoleService.getInstance().find(resultSet.getInt("role_id")),
                    ResourceService.getInstance().find(resultSet.getInt("resource_id")));
        }
        JdbcHelper.close(resultSet,pstmt);
        return resourceAssRole;
    }

    public Collection<Resource> findResourcesByRoleId(int id, Connection connection) throws SQLException {
        Collection<Resource> resources = new TreeSet<>();
        //创建sql语句
        String find_sql = "select resource_id from resource_ass_role where role_id = ?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //根据id查找
        ResultSet resultSet = pstmt.executeQuery();
        while(resultSet.next()) {
            int resource_id = resultSet.getInt("resource_id");
            Resource resource = ResourceService.getInstance().find(resource_id);
            resources.add(resource);
        }
        JdbcHelper.close(resultSet,pstmt);
        return resources;
    }

    // 更新
    public boolean update(ResourceAssRole resourceAssRole, Connection connection) throws SQLException {
        //创建sql语句
        String updateResourceAssRole_sql = "update resource_ass_role set role_id = ?,resource_id = ? where id = ?";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateResourceAssRole_sql);
        //为预编译参数赋值
        pstmt.setInt(3,resourceAssRole.getId());
        pstmt.setInt(1,resourceAssRole.getRole().getId());
        pstmt.setInt(2,resourceAssRole.getResource().getId());
        //根据执行结果，具体判断所要返回的值
        if (pstmt.executeUpdate()==0){
            return false;
        }else {
            return true;
        }
    }

    // 添加
    public boolean add(ResourceAssRole resourceAssRole, Connection connection) throws SQLException {
        //创建sql语句
        String addResourceAssRole_sql =
                "insert into Resource_ass_role (role_id,resource_id) values " +
                        "(?,?);";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(addResourceAssRole_sql);
        //为预编译参数赋值
        pstmt.setInt(1,resourceAssRole.getRole().getId());
        pstmt.setInt(2,resourceAssRole.getResource().getId());
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //根据结果，具体判断所要返回的值
        if (affectedRowNum == 1) {
            return true;
        } else {
            return false;
        }
    }

    // 删除
    public boolean delete(int id,Connection connection) throws SQLException{
        //创建sql语句
        String deleteResourceAssRole_sql = "DELETE FROM Resource_ass_role WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteResourceAssRole_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        if (pstmt.executeLargeUpdate() == 1){
            return true;
        }else {
            return false;
        }
    }

    // 清空表
    public void truncate(Connection connection) throws SQLException {
        String sql = "TRUNCATE TABLE resource_ass_role";
        Statement statement = connection.createStatement();
        statement.executeUpdate(sql);
    }
}
